package com.zkyc.framework.common.mapper;

import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zkyc.framework.common.pojo.ColumnDataClickRate;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface ColumnDataClickRateMapper extends BaseMapper<ColumnDataClickRate> {

    @Select("<script>" +
            "SELECT a.column_data_id, SUM(a.click_rate) as click_rate, b.title as content_title from web_column_data_click_rate a RIGHT JOIN web_column_data b on " +
            "a.column_data_id = b.id and b.status = 1 and b.column_id in" +
            "<foreach collection='columnIdList' item='item' index='index' open='(' close=')' separator=','>" +
            "            #{item}" +
            "</foreach>" +
            "where date_format(click_date,'%Y-%m-%d') &lt; date_format(now(),'%Y-%m-%d') and " +
            "date_format(click_date,'%Y-%m-%d') &gt;= date_format(date_sub(now(), interval 7 day),'%Y-%m-%d') GROUP BY" +
            " column_data_id ORDER BY click_rate desc limit 5" +
            "</script>")
    List<JSONObject> getTop5(List<Integer> columnIdList);

    @Select("<script>" +
            "SELECT column_data_id AS columnDataId, MAX(click_rate_total) AS clickRateTotal " +
            "FROM web_column_data_click_rate GROUP BY column_data_id HAVING column_data_id IN" +
            "<foreach collection='columnDataIdList' item='item' index='index' open='(' close=')' separator=','>" +
            "            #{item}" +
            "</foreach>" +
            "</script>")
    List<ColumnDataClickRate> selectRecent(List<Integer> columnDataIdList);
}
